package com.kim.rssreader.db;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;

import com.google.code.rome.android.repackaged.com.sun.syndication.feed.synd.SyndContent;
import com.google.code.rome.android.repackaged.com.sun.syndication.feed.synd.SyndContentImpl;
import com.google.code.rome.android.repackaged.com.sun.syndication.feed.synd.SyndEntry;
import com.google.code.rome.android.repackaged.com.sun.syndication.feed.synd.SyndEntryImpl;
import com.kim.rssreader.domain.AbstractDomain;
import com.kim.rssreader.domain.RssCategory;
import com.kim.rssreader.domain.RssColumn;
import com.kim.rssreader.domain.RssContent;
import com.kim.rssreader.domain.RssSubscribetion;
import com.kim.rssreader.domain.constant.CityWeatherCode;
import com.kim.rssreader.domain.constant.RssCategoryConstant;
import com.kim.rssreader.domain.constant.RssColumnConstant;
import com.kim.rssreader.domain.constant.RssContentConstant;
import com.kim.rssreader.domain.constant.RssImageConstant;
import com.kim.rssreader.domain.constant.SubscriptionContant;
import com.kim.rssreader.utils.Base64Util;
import com.kim.rssreader.utils.DateUtil;
import com.kim.rssreader.utils.DigestUtils;
import com.kim.rssreader.utils.HtmlTagFilter;

public class DBDAO {

	public static final String TAG = "DBDAO";

	static DBUtils dbUtils;
	static DBDAO instance;

	private DBDAO() {
	}

	public static DBDAO getInstance(Context context) {
		if (dbUtils == null) {
			dbUtils = new DBUtils(context);
		}
		if (instance == null) {
			instance = new DBDAO();
		}
		return instance;
	}

	/**
	 * 查询是栏目是否订阅
	 */
	public boolean checkIsSubcribe(String columnId) {
		String sql = "select count(*) from " + SubscriptionContant.TABLE_NAME
				+ " where _columnid = ?";
		Cursor cursor = null;
		try {
			dbUtils.openReadableDatabase();
			cursor = dbUtils.rowQuery(sql, new String[] { columnId });
			if (cursor != null && cursor.moveToNext()) {
				return dbUtils.getInt(cursor, 0) > 0;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			dbUtils.closeDB();
		}

		return false;
	}

	/**
	 * 更新rss栏目关注度
	 * 
	 * @param columnId
	 */
	public void updateColumnAttention(String columnId) {
		String sql = "UPDATE rss_subscription SET _columnattention = _columnattention+1 WHERE _columnid = ?";
		try {
			dbUtils.openWriteableDatabase();
			dbUtils.executeSql(sql, new String[] { columnId });
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbUtils.closeDB();
		}
	}

	/**
	 * 更新rss类别关注度
	 * 
	 * @param categoryId
	 */
	public void updateCategoryAttention(String categoryId) {
		String sql = "UPDATE rss_category SET _categoryattention = _categoryattention+1 WHERE _categoryid = ?";
		try {
			dbUtils.openWriteableDatabase();
			dbUtils.executeSql(sql, new String[] { categoryId });
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbUtils.closeDB();
		}
	}

	/**
	 * 以base64保存RSS内容
	 * 
	 * @param content
	 * @param rssItemUrl
	 */
	public void updateRSSContent(String content, String rssItemUrl) {
		String saveContent = Base64Util.getBase64EncodingData(content);
		if (saveContent == null) {
			return;
		}
		try {
			ContentValues values = new ContentValues();
			values.put(RssContentConstant.CONTENT, saveContent);
			dbUtils.openWriteableDatabase();
			dbUtils.updateData(RssContentConstant.TABLE_NAME, values,
					RssContentConstant.CONTENTURL + "=?",
					new String[] { rssItemUrl });
			values = null;
			saveContent = null;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbUtils.closeDB();
		}
	}

	/**
	 * 插入rss栏目信息
	 * 
	 * @param sqls
	 */
	public void insertRssColumn(String[] sqls) {
		try {
			dbUtils.openWriteableDatabase();
			dbUtils.insertDataBatch(sqls);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbUtils.closeDB();
		}
	}

	/**
	 * 插入文章列表
	 * 
	 * @param feed
	 * @param columnId
	 */
	public void insertColumnItemList(List<SyndEntry> entries,
			List<SyndEntry> entriesLocal, String columnId) {
		if (entries == null || entries.size() == 0) {
			return;
		}

		HashMap<String, String> titles = new HashMap<String, String>();
		if (entriesLocal != null && entriesLocal.size() > 0) {
			String title = null;
			for (SyndEntry entry : entriesLocal) {
				title = entry.getTitle();
				titles.put(title, title);
			}
		}

		String insertSqlTemplate = "insert into "
				+ RssContentConstant.TABLE_NAME
				+ " ('_contentid', '_contenttitle', '_contenturl', '_contentdescription', '_content', '_contenttime', '_colunmid') "
				+ "values " + "('%s','%s','%s','%s','%s','%s','%s')";

		List<String> sqls = new ArrayList<String>();

		for (SyndEntry entry : entries) {

			String titleBase64 = HtmlTagFilter.filter(entry.getTitle(), true);

			if (titles.get(titleBase64) != null) {
				continue;
			}

			titleBase64 = Base64Util.getBase64EncodingData(titleBase64);

			// 描述信息采用base64加密
			String descriptionBase64 = entry.getDescription().getValue();
			descriptionBase64 = Base64Util
					.getBase64EncodingData(descriptionBase64);

			String timeStr = DateUtil.parseDate2Str(entry.getPublishedDate());
			if (timeStr == null) { // 若无日期信息，即使用当前日期信息
				timeStr = DateUtil.parseDate2Str(new Date());
			}

			String temp = String.format(insertSqlTemplate,
					DigestUtils.shaHex(entry.getTitle()), titleBase64,
					entry.getLink(), descriptionBase64, "", timeStr, columnId);
			sqls.add(temp);
		}

		if (sqls.size() == 0) {
			return;
		}

		try {
			String[] sqlsStr = new String[] {};
			dbUtils.openWriteableDatabase();
			dbUtils.insertDataBatch(sqls.toArray(sqlsStr));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbUtils.closeDB();
		}

	}

	/**
	 * 插入rss文章内容中的图片地址
	 * 
	 * @param contentUrl
	 * @param imageUrl
	 */
	public void insertImageByUrl(String contentUrl, String imageUrl) {
		// String insertSql = "insert into " + RssImageConstant.TABLE_NAME
		// + " ('_rssimageurl', '_rssimagesize', '_contenturl') "
		// + "values " + "('%s','%s','%s')";
		// try {
		// dbUtils.openWriteableDatabase();
		// dbUtils.executeSql(insertSql, new String[] { imageUrl, "0",
		// contentUrl });
		// } catch (Exception e) {
		// e.printStackTrace();
		// } finally {
		// dbUtils.closeDB();
		// }

		ContentValues contentValues = new ContentValues();
		contentValues.put(RssImageConstant.RSSIMAGEURL, imageUrl);
		contentValues.put(RssImageConstant.RSSIMAGESIZE, "0");
		contentValues.put(RssImageConstant.CONTENTURL, contentUrl);
		try {
			dbUtils.openWriteableDatabase();
			dbUtils.insertData(RssImageConstant.TABLE_NAME, contentValues);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbUtils.closeDB();
		}
	}

	/**
	 * 添加订阅关系列表
	 * 
	 * @param columnId
	 * @param columnName
	 */
	public void insertSubscription(String columnId, String time) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(SubscriptionContant.COLUMNID, columnId);
		contentValues.put(SubscriptionContant.ADDDATE, time);
		try {
			dbUtils.openWriteableDatabase();
			dbUtils.insertData(SubscriptionContant.TABLE_NAME, contentValues);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbUtils.closeDB();
		}

	}

	/**
	 * 删除订阅关系
	 * 
	 * @param columnId
	 * @return
	 */

	public void delSubscription(String columnId) {
		String delSql = "delete from " + SubscriptionContant.TABLE_NAME
				+ " where " + SubscriptionContant.COLUMNID + " = ? ";
		try {
			dbUtils.openWriteableDatabase();
			dbUtils.executeSql(delSql, new String[] { columnId });
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbUtils.closeDB();
		}
	}

	public List<SyndEntry> getColumnItemByColumnId(String columnId) {

		dbUtils.openReadableDatabase();
		Cursor cursor = dbUtils.checkData(RssContentConstant.TABLE_NAME,
				RssContentConstant.COLUMNS, RssContentConstant.COLUNMID + "=?",
				new String[] { columnId }, null, null,
				RssContentConstant.CONTENTTIME + " desc");

		try {
			if (cursor != null && cursor.getCount() > 0) {
				SyndEntry syndEntry = null;
				List<SyndEntry> syndEntries = new ArrayList<SyndEntry>();
				while (cursor.moveToNext()) {
					syndEntry = new SyndEntryImpl();

					// 标题经过base64编码，需解码
					String titleOrignal = dbUtils.getString(cursor,
							RssContentConstant.CONTENT_CONTENTTITLE);
					titleOrignal = Base64Util
							.getBase64DecodingData(titleOrignal);
					syndEntry.setTitle(titleOrignal);

					syndEntry.setLink(dbUtils.getString(cursor,
							RssContentConstant.CONTENT_CONTENTURL));
					syndEntry.setPublishedDate(DateUtil.getDate(dbUtils
							.getString(cursor,
									RssContentConstant.CONTENT_CONTENTTIME)));
					SyndContent description = new SyndContentImpl();

					// 描述信息经过base64编码，需解码
					String descriptionOrignal = dbUtils.getString(cursor,
							RssContentConstant.CONTNT_CONTENTDESCRIPTION);
					descriptionOrignal = Base64Util
							.getBase64DecodingData(descriptionOrignal);

					description.setValue(descriptionOrignal);
					syndEntry.setDescription(description);

					// 内容信息经过base64编码，需解码
					String base64Str = dbUtils.getString(cursor,
							RssContentConstant.CONTENT_CONTENT);
					String content = Base64Util
							.getBase64DecodingData(base64Str);

					List<String> contents = new ArrayList<String>();
					contents.add(content);
					syndEntry.setContents(contents);
					syndEntries.add(syndEntry);
				}
				return syndEntries;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			dbUtils.closeDB();
		}
		return null;
	}

	/**
	 * 查询Rss类别
	 * 
	 * @return {@link RssCategory}
	 */
	public List<AbstractDomain> getRssCategory() {
		dbUtils.openReadableDatabase();
		Cursor cursor = dbUtils.checkData(RssCategoryConstant.TABLE_NAME,
				RssCategoryConstant.COLUMNS, null, null, null, null,
				RssCategoryConstant.CATEGORYATTENTION + " desc");
		try {
			if (cursor != null && cursor.getCount() > 0) {
				List<AbstractDomain> rssCategories = new ArrayList<AbstractDomain>();
				RssCategory category = null;
				while (cursor.moveToNext()) {
					category = new RssCategory();
					category.rssCategoryId = dbUtils.getString(cursor,
							RssCategoryConstant.CATEGORY_CATEGORYID);
					category.rssCategoryName = dbUtils.getString(cursor,
							RssCategoryConstant.CATEGORY_CATEGORYNAME);
					category.rssCategoryCustomer = dbUtils.getBoolean(cursor,
							RssCategoryConstant.category_CATEGORYCUSTOMER);
					category.rssCategoryAttention = dbUtils.getInt(cursor,
							RssCategoryConstant.CATEGORY_CATEGORYATTENTION);
					rssCategories.add(category);
				}
				return rssCategories;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			dbUtils.closeDB();
		}
		return null;
	}

	/**
	 * 查询订阅关系列表
	 * 
	 * @return {@link RssCategory}
	 */
	public List<AbstractDomain> getSubscrbetion() {
		dbUtils.openReadableDatabase();
		Cursor cursor = dbUtils.checkData(SubscriptionContant.TABLE_NAME,
				SubscriptionContant.COLUMNS, null, null, null, null,
				SubscriptionContant.ADDDATE + " desc");
		try {
			if (cursor != null && cursor.getCount() > 0) {
				List<AbstractDomain> rssSubscription = new ArrayList<AbstractDomain>();
				RssSubscribetion subscription = null;
				while (cursor.moveToNext()) {
					subscription = new RssSubscribetion();
					subscription.columnId = dbUtils.getString(cursor,
							RssCategoryConstant.CATEGORY_CATEGORYID);
					subscription.columnName = dbUtils.getString(cursor,
							RssCategoryConstant.CATEGORY_CATEGORYNAME);
					subscription.addDate = dbUtils.getBoolean(cursor,
							RssCategoryConstant.category_CATEGORYCUSTOMER);
					rssSubscription.add(subscription);
				}
				return rssSubscription;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			dbUtils.closeDB();
		}
		return null;
	}

	/**
	 * 
	 * @param categoryId
	 *            RSS类别ID
	 * @return {@link RssColumn}
	 */
	public List<AbstractDomain> getColumnByCategory(String categoryId,
			boolean isFrequent) {
		String orderby = RssColumnConstant.RSSCOLUMNATTENTION + " desc";
		dbUtils.openReadableDatabase();
		Cursor cursor = null;
		if (isFrequent) {
			cursor = dbUtils.checkData(RssColumnConstant.TABLE_NAME,
					RssColumnConstant.COLUMNS, null, null, null, null, orderby);
		} else {
			cursor = dbUtils.checkData(RssColumnConstant.TABLE_NAME,
					RssColumnConstant.COLUMNS,
					RssColumnConstant.RSSCOLUMNCATEGORY + "=?",
					new String[] { categoryId }, null, null, orderby);
		}

		try {
			if (cursor != null && cursor.getCount() > 0) {
				List<AbstractDomain> rssCategories = new ArrayList<AbstractDomain>();
				RssColumn column = null;
				while (cursor.moveToNext()) {
					column = new RssColumn();
					column.rssColumnID = dbUtils.getString(cursor,
							RssColumnConstant.COLUMN_RSSCOLUMNID);
					column.rssColumnName = dbUtils.getString(cursor,
							RssColumnConstant.COLUMN_RSSCOLUMNNAME);
					column.rssColumnUrl = dbUtils.getString(cursor,
							RssColumnConstant.COLUMN_RSSCOLUMNURL);
					column.rssColumnEncoding = dbUtils.getString(cursor,
							RssColumnConstant.COLUMN_RSSCOLUMNENCODING);
					column.rssColumncustomer = dbUtils.getBoolean(cursor,
							RssColumnConstant.COLUMN_RSSCOLUMNCUSTOMER);
					column.rssColumnAttention = dbUtils.getInt(cursor,
							RssColumnConstant.COLUMN_RSSCOLUMNATTENTION);
					column.rssColumnFlagStart = dbUtils.getString(cursor,
							RssColumnConstant.COLUMN_RSSCOLUMNFLAGSTART);
					column.rssColumnFlagEnd = dbUtils.getString(cursor,
							RssColumnConstant.COLUMN_RSSCOLUMNFLAGEND);
					column.rssColumnCategory = dbUtils.getString(cursor,
							RssColumnConstant.COLUMN_RSSCOLUMNCATEGORY);
					rssCategories.add(column);
				}
				return rssCategories;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			dbUtils.closeDB();
		}
		return null;
	}

	/**
	 * 查询已订阅栏目
	 * 
	 * @return
	 */
	public List<RssColumn> getSubscribedRssColumn() {
		// select a.* from rss_column a, rss_subscription b where a._columnid =
		// b._columnid
		String sql = "select a.* from " + RssColumnConstant.TABLE_NAME + " a,"
				+ SubscriptionContant.TABLE_NAME + " b where a."
				+ RssColumnConstant.RSSCOLUMNID + " = b."
				+ SubscriptionContant.COLUMNID + " order by b."
				+ SubscriptionContant.COLUMNATTENTION + " desc";
		Cursor cursor = null;
		try {
			dbUtils.openReadableDatabase();
			cursor = dbUtils.rowQuery(sql, null);
			if (cursor != null && cursor.getCount() > 0) {
				List<RssColumn> columns = new ArrayList<RssColumn>();
				RssColumn column = null;
				while (cursor.moveToNext()) {
					column = new RssColumn();
					column.rssColumnID = dbUtils.getString(cursor,
							RssColumnConstant.COLUMN_RSSCOLUMNID);
					column.rssColumnName = dbUtils.getString(cursor,
							RssColumnConstant.COLUMN_RSSCOLUMNNAME);
					column.rssColumnUrl = dbUtils.getString(cursor,
							RssColumnConstant.COLUMN_RSSCOLUMNURL);
					column.rssColumnEncoding = dbUtils.getString(cursor,
							RssColumnConstant.COLUMN_RSSCOLUMNENCODING);
					column.rssColumncustomer = dbUtils.getBoolean(cursor,
							RssColumnConstant.COLUMN_RSSCOLUMNCUSTOMER);
					column.rssColumnAttention = dbUtils.getInt(cursor,
							RssColumnConstant.COLUMN_RSSCOLUMNATTENTION);
					column.rssColumnFlagStart = dbUtils.getString(cursor,
							RssColumnConstant.COLUMN_RSSCOLUMNFLAGSTART);
					column.rssColumnFlagEnd = dbUtils.getString(cursor,
							RssColumnConstant.COLUMN_RSSCOLUMNFLAGEND);
					column.rssColumnCategory = dbUtils.getString(cursor,
							RssColumnConstant.COLUMN_RSSCOLUMNCATEGORY);
					columns.add(column);
				}
				return columns;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			dbUtils.closeDB();
		}
		return null;
	}

	/**
	 * 根据url获取本地内容缓存
	 * 
	 * @param url
	 * @return
	 */
	public String getRssContentByUrl(String url) {

		dbUtils.openReadableDatabase();
		Cursor cursor = dbUtils.checkData(RssContentConstant.TABLE_NAME,
				new String[] { RssContentConstant.CONTENT },
				RssContentConstant.CONTENTURL + "=?", new String[] { url },
				null, null, null);

		try {
			if (cursor != null && cursor.getCount() > 0) {
				if (cursor.moveToNext()) {
					// 内容信息经过base64编码，需解码
					String base64Str = dbUtils.getString(cursor, 0);
					String content = Base64Util
							.getBase64DecodingData(base64Str);
					return content;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			dbUtils.closeDB();
		}
		return null;
	}

	public RssContent getRssContentEntityByUrl(String url) {
		dbUtils.openReadableDatabase();
		Cursor cursor = dbUtils.checkData(RssContentConstant.TABLE_NAME,
				RssContentConstant.COLUMNS, RssContentConstant.CONTENTURL
						+ "=?", new String[] { url }, null, null, null);

		try {
			if (cursor != null && cursor.getCount() > 0) {
				if (cursor.moveToNext()) {
					RssContent content = new RssContent();

					// 标题经过base64编码，需解码
					String titleOrignal = dbUtils.getString(cursor,
							RssContentConstant.CONTENT_CONTENTTITLE);
					titleOrignal = Base64Util
							.getBase64DecodingData(titleOrignal);
					content.rssContentTitle = titleOrignal;
					content.rssContentUrl = dbUtils.getString(cursor,
							RssContentConstant.CONTENT_CONTENTURL);
					content.rssContentTime = DateUtil.parseDate2Str(DateUtil
							.getDate(dbUtils.getString(cursor,
									RssContentConstant.CONTENT_CONTENTTIME)));

					// 描述信息经过base64编码，需解码
					String descriptionOrignal = dbUtils.getString(cursor,
							RssContentConstant.CONTNT_CONTENTDESCRIPTION);
					descriptionOrignal = Base64Util
							.getBase64DecodingData(descriptionOrignal);
					content.rssContentDescription = descriptionOrignal;
					// 内容信息经过base64编码，需解码
					String base64Str = dbUtils.getString(cursor,
							RssContentConstant.CONTENT_CONTENT);
					String contentStr = Base64Util
							.getBase64DecodingData(base64Str);
					content.rssContent = contentStr;

					return content;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			dbUtils.closeDB();
		}
		return null;
	}

	/**
	 * 获取文章内容中的图片地址
	 * 
	 * @param contentUrl
	 * @return
	 */
	public List<String> getContentImageUrls(String contentUrl) {
		Cursor cursor = null;
		try {
			dbUtils.openReadableDatabase();
			cursor = dbUtils.checkData(RssImageConstant.TABLE_NAME,
					new String[] { RssImageConstant.RSSIMAGEURL },
					RssImageConstant.CONTENTURL + "=?",
					new String[] { contentUrl }, null, null, null);
			if (cursor != null && cursor.getCount() > 0) {
				List<String> imgUrlArray = new ArrayList<String>();
				while (cursor.moveToNext()) {
					String url = dbUtils.getString(cursor, 0);
					imgUrlArray.add(url);
				}
				return imgUrlArray;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			dbUtils.closeDB();
		}
		return null;
	}

	/**
	 * 根据url更新对应文章阅读状态 将文章标记为已读
	 * 
	 * @param url
	 */
	public int updateReadState(String url) {
		dbUtils.openWriteableDatabase();
		ContentValues values = new ContentValues();
		try {
			values.put(RssContentConstant.CONTENTREAD, true);
			return dbUtils.updateData(RssContentConstant.TABLE_NAME, values,
					RssContentConstant.CONTENTURL + "=?", new String[] { url });
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbUtils.closeDB();
			values = null;
		}
		return -1;
	}

	/**
	 * 读取url对应文章的阅读状态
	 * 
	 * @param url
	 * @return
	 */
	public boolean getReadState(String url) {
		dbUtils.openReadableDatabase();
		Cursor cursor = dbUtils.checkData(RssContentConstant.TABLE_NAME,
				new String[] { RssContentConstant.CONTENTREAD },
				RssContentConstant.CONTENTURL + "=?", new String[] { url },
				null, null, null);

		try {
			if (cursor != null && cursor.getCount() > 0) {
				if (cursor.moveToNext()) {
					return dbUtils.getBoolean(cursor, 0);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			dbUtils.closeDB();
		}
		return false;
	}

	/**
	 * 读取城市对应的天气代码
	 * 
	 * @param url
	 * @return
	 */
	public String getWeather(String cityname) {
		dbUtils.openReadableDatabase();
		Cursor cursor = dbUtils.checkData(CityWeatherCode.TABLE_NAME,
				new String[] { CityWeatherCode.CODE },
				CityWeatherCode.CITY + "=?", new String[] { cityname },
				null, null, null);

		try {
			if (cursor != null ) {
				if (cursor.moveToNext()) {
					return dbUtils.getString(cursor, 0);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			dbUtils.closeDB();
		}
		return "";
	}

}
